
IF OBJECT_ID('dp_listtask') IS NOT NULL
		DROP PROC dbo.dp_listtask
GO

CREATE PROC dbo.dp_listtask
		@pCustID	INT		-- 客户ID
AS
BEGIN
		SET NOCOUNT ON
/**
		列出指定客户当前的任务
		2019-10-02 22:31:50 修改和优化
		2019-10-08 21:26:53 修改和优化 . 注意, 本过程急需优化! 数据量一多性能下降可能会很快!
		2019-11-01 23:35:15 V2
*/
		IF ISNULL(@pCustID, 0) <= 0 SET @pCustID = 0

		DECLARE @fNow DATETIME = GETDATE(), @fTime TIME

		-- 测试
		--SET @fNow = '2019-09-28 04:05:00'
		-- SET @fNow = '2019-09-28 04:05:00'
		
		SET @fTime = CAST(@fNow AS TIME)

		DECLARE @tResult TABLE(sID INT NULL)
		DECLARE @fLastDailyTime DATETIME, @fLastIntervalTime DATETIME

		

		-- 2019-10-15 00:20:00 首先,获取临时指定的任务
		INSERT INTO @tResult (sID)
		SELECT rQuery_ID
		FROM dbo.dt_schedule
		WHERE rCust_ID = @pCustID AND xDateTime <= @fNow AND xStatus = 1

		-- UPDATE dbo.wt_schedule SET xStatus = 2 WHERE rCust_ID = @pCustID AND xDateTime <= @fNow AND xStatus = 1
		-- 获取到了就转手把它放到历史记录里, 不管结果
		INSERT INTO dbo.dt_schedule_log (sID, rCust_ID, xDateTime, xType, rQuery_ID, xStatus, sCreateTime, sLastUpdate, sVersion)
		SELECT sID, rCust_ID, xDateTime, xType, rQuery_ID, 2, sCreateTime, @fNow, sVersion + 1
		FROM dbo.dt_schedule WHERE rCust_ID = @pCustID AND xDateTime <= @fNow AND xStatus = 1 AND rQuery_ID IN (SELECT sID FROM @tResult)
		-- 清理它
		DELETE FROM dbo.dt_schedule WHERE rCust_ID = @pCustID AND xDateTime <= @fNow AND xStatus = 1 AND rQuery_ID IN (SELECT sID FROM @tResult)

		-- 先不支持每月,每周任务

		-- 如果是凌晨12点到6点之间, 则只执行每天一次的任务, 而且每天只有一次;
		IF @fTime BETWEEN '00:00:00' AND '06:00:00'
		BEGIN
				
				-- 只在凌晨3~5点会返回执行指令! 其余时间用于其他维护!
				-- 客户端会保证,只有执行完上一次的任务后才会定时心跳,因此,这个定时任务判断至少距离上一次6个小时是合适的
				IF @fTime BETWEEN '03:00:00' AND '05:00:00'
				BEGIN
						SELECT @fLastDailyTime = m.xLastDailyTime FROM dbo.dt_customer AS m (NOLOCK) WHERE m.sID = @pCustID

						-- 没有上一次的任务, 或者上一次任务距离现在超过6个小时了
						IF @fLastDailyTime IS NULL OR DATEDIFF(SECOND, @fLastDailyTime, @fNow) > 21600	-- 6个小时
						BEGIN
								INSERT INTO @tResult (sID) SELECT m.sID FROM dbo.dt_query AS m (NOLOCK) WHERE m.xInterval = 4
						END
				END
				
		END
		ELSE
		BEGIN
				-- 如果是早上6点到晚上12点之间,则返回固定周期的任务
				--WITH rs AS (
						
				--)

				SELECT @fLastIntervalTime = m.xLastIntervalTime FROM dbo.dt_customer AS m (NOLOCK) WHERE m.sID = @pCustID
				INSERT INTO @tResult (sID)
				SELECT m.sID
				FROM dbo.dt_query AS m (NOLOCK)
				WHERE	m.xInterval IN (5, 6, 7, 8)
					AND (	@fLastIntervalTime IS NULL 
							OR DATEDIFF(SECOND, @fLastIntervalTime, @fNow) > 
									--                1h               10min           5min           1min            1h
									CASE m.xType WHEN 5 THEN 3600 WHEN 6 THEN 600 WHEN 7 THEN 300 WHEN 8 THEN 60 ELSE 3600 END
						)
						-- 至少距离上次1个小时


		END

		-- 2019-09-29 22:01:34 F 增加: 如果上次心跳时间距离现在超过1个小时, 则要求上报服务器信息	-- 不妥, 万一是执行某个任务超过1个小时呢

		--SELECT @rResult = CASE WHEN @rResult IS NULL OR @rResult = '' THEN '' ELSE @rResult + ',' END + CAST(m.sID AS NVARCHAR(30)) FROM @tResult AS m ORDER BY m.sID
		--IF @rResult IS NULL SET @rResult = ''
		SELECT DISTINCT sID FROM @tResult ORDER BY sID ASC
END

GO
